package com.edu.hre.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.edu.hre.db.config.BaseDAO;
import com.edu.hre.db.config.DBPool;
import com.edu.hre.db.dto.HreLeaveRequest;

public class LeaveDAO extends BaseDAO {
	private static final Logger logger = Logger.getLogger(LeaveDAO.class);

	public List<HreLeaveRequest> getLeaveListBySupId(int employeeNumber) {
		List<HreLeaveRequest> listLeaveRequests = new ArrayList<HreLeaveRequest>();
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = "SELECT lr.*,"
					+ "lt.name AS leave_type,"
					+ "CONCAT(e.emp_lastname,' ',e.emp_middle_name,' ',e.emp_firstname) AS emp_full_name"
					+ " FROM hre_leave_request lr "
					+ "JOIN hre_leave_type lt ON lr.leave_type_id = lt.id "
					+ "JOIN hre_employee e ON lr.emp_num = e.emp_number "
					+ "WHERE lr.approved IS NULL AND lr.supervisor_num = "
					+ employeeNumber;
			preStmt = conn.prepareStatement(strSQLQuery);
			rs = preStmt.executeQuery();
			while (rs.next()) {
				HreLeaveRequest leaveRequest = new HreLeaveRequest();
				leaveRequest.setFirstLeave(rs.getTimestamp("first_leave"));
				leaveRequest.setLastLeave(rs.getTimestamp("last_leave"));
				leaveRequest.setLeaveType(rs.getString("leave_type"));
				leaveRequest.setEmpFullName(rs.getString("emp_full_name"));
				leaveRequest.setEmpComment(rs.getString("emp_comment"));
				leaveRequest.setId(rs.getInt("id"));
				// .....
				// .....
				listLeaveRequests.add(leaveRequest);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào
			// pool
			releaseConnection(conn, preStmt, rs);
		}
		return listLeaveRequests;
	}

	public List<HreLeaveRequest> getLeaveListByUserId(int employeeNumber) {
		List<HreLeaveRequest> listLeaveRequests = new ArrayList<HreLeaveRequest>();
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = "SELECT lr.*,"
					+ "lt.name AS leave_type,"
					+ "CONCAT(e.emp_lastname,' ',e.emp_middle_name,' ',e.emp_firstname) AS emp_full_name"
					+ " FROM hre_leave_request lr "
					+ "JOIN hre_leave_type lt ON lr.leave_type_id = lt.id "
					+ "JOIN hre_employee e ON lr.supervisor_num = e.emp_number "
					+ "WHERE lr.emp_num = " + employeeNumber
					+ " ORDER BY date_created DESC";
			preStmt = conn.prepareStatement(strSQLQuery);
			rs = preStmt.executeQuery();
			while (rs.next()) {
				HreLeaveRequest leaveRequest = new HreLeaveRequest();
				leaveRequest.setFirstLeave(rs.getTimestamp("first_leave"));
				leaveRequest.setLastLeave(rs.getTimestamp("last_leave"));
				leaveRequest.setLeaveType(rs.getString("leave_type"));
				leaveRequest.setEmpFullName(rs.getString("emp_full_name"));
				leaveRequest.setEmpComment(rs.getString("emp_comment"));
				leaveRequest.setSupComment(rs.getString("sup_comment"));
				leaveRequest.setApproved(rs.getBoolean("approved"));
				if (rs.wasNull()) {
					leaveRequest.setApproved(null);
				}
				leaveRequest.setId(rs.getInt("id"));
				// .....
				// .....
				listLeaveRequests.add(leaveRequest);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào
			// pool
			releaseConnection(conn, preStmt, rs);
		}
		return listLeaveRequests;
	}

	public void updateLeave(String id, String approved, String comment) {
		Connection conn = null;
		PreparedStatement preStmt = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = "UPDATE hre_leave_request SET"
					+ " approved = " + approved + " ,sup_comment = '" + comment
					+ "' WHERE id =" + id;
			System.out.println(strSQLQuery);
			preStmt = conn.prepareStatement(strSQLQuery);
			preStmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào
			// pool
			releaseConnection(conn, preStmt);
		}
	}

	public void InsertLeaveRequest(int userId, int supervisorId,
			int leaveTypeId, String leaveFromStr, String leaveToStr,
			String empComment) {
		Connection conn = null;
		PreparedStatement preStmt = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = "INSERT INTO hre_leave_request(emp_num,supervisor_num,leave_type_id,first_leave,last_leave,emp_comment,date_created,date_modified) VALUE"
					+ "(" + userId + "," + supervisorId + "," + leaveTypeId +",\'"+leaveFromStr+"\',\'"+leaveToStr+"\',\'"+empComment+"\'"+",NOW(),NOW())";
			System.out.println(strSQLQuery);
			preStmt = conn.prepareStatement(strSQLQuery);
			preStmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào
			// pool
			releaseConnection(conn, preStmt);
		}
	}

}
